﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Runtime.InteropServices;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading;
using System.Xml;
using Common.Utilities;
using Common.Utilities.Dispatcher;
using Common.Utilities.OpenXml;
using DataTable = System.Data.DataTable;

namespace Workflows.Components.ETL
{
	/// <summary>
	/// 
	/// </summary>
	public class ExcelReaderWorker:IWorker 
	{
		#region Implementation of IWorker

		public event WorkerStarted WorkerStartedEvent;
		public event WorkerFinished WorkerFinishedEvent;
		public event WorkerHasError WorkerErredEvent;
		public event WorkerCancelled WorkerCancelledEvent;
		public event WorkerStatusChanged WorkerStatusChangedEvent;

		private Thread workerThread;
		private ExcelReaderInstruction instruction;
		private ExcelReaderOutput output;
		private RunContext _Context;
		private Guid _TicketID;
		private string _WorkflowName;
		private string _ComponentName;

		/// <summary>
		/// 
		/// </summary>
		public string ComponentName
		{
			get { return _ComponentName; }
		}

		/// <summary>
		/// 
		/// </summary>
		public string WorkflowName
		{
			get { return _WorkflowName; }
		}

		/// <summary></summary>
		public Thread WorkerThread
		{
			get { return workerThread; }
		}
		/// <summary></summary>
		public InstructionBase Instruction
		{
			get { return instruction; }
		}
		/// <summary></summary>
		public ResultBase Output
		{
			get { return output; }
		}
		/// <summary></summary>
		public Type ComponentType
		{
			get { return typeof(ExcelReaderComponent); }
		}
		/// <summary></summary>
		public RunContext Context
		{
			get { return _Context; }
			set { _Context = value; }
		}
		/// <summary></summary>
		public void SetInstruction(Guid ticketGuid, InstructionBase request, string workflowName, string componentName)
		{
			this._TicketID = ticketGuid;
			this.instruction = (ExcelReaderInstruction)request;
			this._WorkflowName = workflowName;
			this._ComponentName = componentName;
		}
		/// <summary></summary>
		public void SetInstruction(RunContext context, Guid ticketGuid, InstructionBase request, string workflowName, string componentName)
		{
			this._TicketID = ticketGuid;
			this.instruction = (ExcelReaderInstruction)request;
			this._Context = context;
			this._WorkflowName = workflowName;
			this._ComponentName = componentName;
		}
		/// <summary></summary>
		public void DoWork()
		{
			if (this.WorkerStartedEvent != null)
			{
				this.WorkerStartedEvent(this._TicketID, this, this.GetType(), this.instruction);
			}
			this.workerThread = Thread.CurrentThread;
			this.ReadExcelFile();
		}
		/// <summary></summary>
		public void DoWorkAsync()
		{
			if (this.WorkerStartedEvent != null)
			{
				this.WorkerStartedEvent(this._TicketID, this, this.GetType(), this.instruction);
			}
			this.workerThread = new Thread(new ThreadStart(this.ReadExcelFile));
			this.workerThread.Start();
		}
		/// <summary></summary>
		public void CancelWork()
		{
			if (this.workerThread != null && this.workerThread.IsAlive)
			{
				this.workerThread.Abort();
				this.workerThread.Join();

				if (this.WorkerCancelledEvent != null)
				{
					this.WorkerCancelledEvent(this._TicketID, this.GetType(), this.instruction);
				}
			}
		}

		#endregion

		#region work 
		private void ReadExcelFile()
		{
			try
			{
				if(!File.Exists(this.instruction.ExcelFilePath))
					throw new Exception("Unable to find excel file");

				DataSet ds=new DataSet();
				foreach(string sheetName in this.instruction.ColumnDataTypes.Keys)
				{
				    DataTable dt = ExcelReader.ReadTable(this.instruction.ExcelFilePath, sheetName);
                    DataTable dtTgt=new DataTable(sheetName);
                    foreach(string colName in this.instruction.ColumnDataTypes[sheetName].Keys)
                    {
                        dtTgt.Columns.Add(colName,
                                          SimpleDataTypeUtil.ToSystemType(
                                              this.instruction.ColumnDataTypes[sheetName][colName]));
                    }
                    foreach(DataRow dr in dt.Rows)
                    {
                        DataRow drTgt = dtTgt.NewRow();
                        foreach(string colName in this.instruction.ColumnDataTypes[sheetName].Keys)
                        {
                            if(dr[colName]!=null && dr[colName]!=DBNull.Value)
                            {
                                object fieldValue =
                                    SimpleDataTypeUtil.ToDbValue(this.instruction.ColumnDataTypes[sheetName][colName],
                                                                 dr[colName].ToString());
                                drTgt[colName] = fieldValue;
                            }
                        }
                        dtTgt.Rows.Add(drTgt);
                    }
				    ds.Tables.Add(dtTgt);
				}

                if(!string.IsNullOrEmpty(this.instruction.OutputFilePath))
                {
                    ds.WriteXml(this.instruction.OutputFilePath);
                }
                else if(!string.IsNullOrEmpty(this.instruction.TgtConnStr))
                {
                    this.SaveToDB(ds);
                }

				this.output = new ExcelReaderOutput(this.instruction.OwnerComponentID, this._TicketID,
				                                    this.instruction.OutputFilePath);
				if (this.WorkerFinishedEvent != null)
					this.WorkerFinishedEvent(this._TicketID, this.GetType(), this.instruction, this.output);
			}
			catch (Exception ex)
			{
				if (this.WorkerErredEvent != null)
					this.WorkerErredEvent(this._TicketID, this.GetType(), this.instruction, ex.ToString(), true);
			}
		}

	    private void SaveToDB(DataSet ds)
	    {
	        if(ds==null || ds.Tables==null || ds.Tables.Count !=1)
                throw new Exception("Unable to save table to db");

	        DataTable dt = ds.Tables[0];
            List<TableColumn> cols=new List<TableColumn>();
	        Dictionary<string, SimpleDataType> dbTypes = this.instruction.ColumnDataTypes[dt.TableName];
	        Dictionary<string, int> sizes = this.instruction.ColumnSizes[dt.TableName];
            foreach(string colName in dbTypes.Keys)
            {
                TableColumn col=new TableColumn();
                col.AllowNull = true;
                col.ColumnName = colName;
                col.DbType = FieldDataType.ToDbType(SimpleDataTypeUtil.ToSystemType(dbTypes[colName]).ToString());
                col.IsFK = false;
                col.IsIdentity = false;
                col.IsIndexed = false;
                col.IsPK = false;
                col.Ordinal = cols.Count;
                col.Precision = 0;
                col.Scale = 0;
                col.Size = sizes[colName];
                cols.Add(col);
            }
            if (this.instruction.RecreateTgtTable)
            {
                DbUtil.NewTable(DataProviderType.MSSQL, this.instruction.TgtConnStr,
                                this.instruction.TgtSchemaName, this.instruction.TgtTableName,
                                cols.ToArray());
            }
            else if(this.instruction.ClearExistingData)
            {
                string sql = string.Format("truncate table {0}.[{1}]",
                                           this.instruction.TgtSchemaName, this.instruction.TgtTableName);
                DbUtil.ExecuteSqlCommand(this.instruction.TgtConnStr,sql);
            }
	        DbUtil.SaveTableData(
                this.instruction.TgtConnStr, this.instruction.TgtSchemaName, 
                this.instruction.TgtTableName, dt, cols);
	    }

	    #endregion 

		
	}
}
